<?php
/**
 * Description of Emms_Report_PortfolioAtRiskByBusiness
 *
 * @author andres
 */
class Emms_Report_PortfolioAtRiskByBusiness extends HTech_Report
{
    private $_par;
    
    public function __construct() {
        $this->_par = explode(',',Zend_Registry::get('HT_Config')->param->par_boundaries); 
        parent::__construct(); 
    }
    
    protected function _setName()
    {
        $this->_name = $this->translate('Portfolio at risk consolidated by business');
    }
    
    protected function _setForm()
    {
        $this->_form = new HTech_Report_Form_Date();
    }
    
    public function _setIndex() 
    {
        $this->_index = 'BusinessTypeId';
    }
    
    protected function _setSql()
    {      
        foreach($this->_par as $delay)
            $this->_sql[] = sprintf('
                select 
                    l.BusinessTypeId,
                    count(a.LoanId) Par%sLoans,
                    sum(a.BalancePrincipal) Par%sMnt
                from
                    arrears a
                left join
                    loans l on l.LoanId = a.LoanId
                where
                    a.Date = :date and a.Delay >= %s
                group by
                    l.BusinessTypeId', $delay, $delay, $delay);
        
        $this->_sql[] = '
            select 
                l.BusinessTypeId,
                sum(p.Principal) PrincipalPaid,
                sum(p.LoanInsurance) LoanInsurancePaid,
                sum(p.HealthInsurance) HealthInsurancePaid,
                sum(p.AdministrativeFee) AdministrativeFeePaid
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                payments p on p.LoanId = l.LoanId and p.Date < :date
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                l.BusinessTypeId';

        $this->_sql[] = '
            select 
                bt.TypeId BusinessTypeId,
                bt.Type BusinessType,
                bt.Activity,
                count(l.LoanId) Loans,
                sum(l.Principal) PrincipalDisbursed,
                sum(l.LoanInsuranceMnt) LoanInsuranceDisbursed,
                sum(l.HealthInsuranceMnt) HealthInsuranceDisbursed,
                sum(l.AdministrativeFeeMnt) AdministrativeFeeDisbursed
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                business_types bt on bt.TypeId = l.BusinessTypeId
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                bt.TypeId
            order by
                bt.Activity, bt.TypeId';
    }

    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->BusinessType = $this->translate('Business');
        $this->_headers->Activity = $this->translate('Activity');
        $this->_headers->Loans = $this->translate('Loans');
        $this->_headers->PrincipalBalance = $this->translate('Principal balance');
        foreach($this->_par as $delay) {
            $lns = sprintf('Par%sLoans',$delay);
            $mnt = sprintf('Par%sMnt',$delay);
            $pct = sprintf('Par%sPct',$delay);
            $this->_headers->$lns = sprintf($this->translate('PAR %s - Loans'),$delay);
            $this->_headers->$mnt = sprintf($this->translate('PAR %s - Amount'),$delay);
            $this->_headers->$pct = sprintf($this->translate('PAR %s - Percentage'),$delay);
        }
    }
    
    protected function _process($data)
    {
        foreach($data as $row) {
            
            $data[$row->BusinessTypeId]->PrincipalBalance = 
                $row->PrincipalDisbursed - $row->PrincipalPaid;
            
            foreach($this->_par as $delay) {
                $lns = sprintf('Par%sLoans',$delay);
                $mnt = sprintf('Par%sMnt',$delay);
                $pct = sprintf('Par%sPct',$delay);
                $data[$row->BusinessTypeId]->$lns = (int) $data[$row->BusinessTypeId]->$lns;
                $data[$row->BusinessTypeId]->$mnt = (double) $data[$row->BusinessTypeId]->$mnt;
                $data[$row->BusinessTypeId]->$pct = round(100*$data[$row->BusinessTypeId]->$mnt/$data[$row->BusinessTypeId]->PrincipalBalance,2);
            }
        }
        return $data;
    }
    
    public function setParams($params) 
    {
        $this->_params = array(':date' => $params['Date']);
    }

}
